const { Service } = require('egg')
const Response = require('../utils/Response')

class orderService extends Service {
  // 信息录入 ---- 新增
  async addStudentInfo(params) {
    const { ctx } = this
    const { mysql } = this.app
    const { name, gender, age, tel, parentTel } = params
    const sql = `INSERT INTO students(stu_name, stu_gender, stu_age, tel, parentTel, curNameList, curCodeList) VALUES (?, ?, ?, ?, ?, ?, ?)`
    try {
      const result = await mysql.query(sql, [name, gender, age, tel, parentTel, '[]', '[]'])
      ctx.body = result.affectedRows === 1 ? new Response('插入成功', 200) : new Response('插入失败', 501)
    } catch (err) {
      throw new Response('插入异常', 500, err)
    }
  }

  // 信息录入 ---- 删除
  async deleteStudentInfo(params) {
    const { ctx } = this
    const { mysql } = this.app
    const sql = `SELECT curNameList FROM students WHERE stu_code=?`
    let cur_list = await mysql.query(sql, [params.code])
    cur_list = JSON.parse(cur_list[0].curNameList)
    if (cur_list.length) {
      ctx.body = new Response('已关联课程，不能删除', 500)
      return
    }
    try {
      const result = await mysql.query(`DELETE FROM students WHERE stu_code=?`, [params.code])
      ctx.body = result.affectedRows === 1 ? new Response('删除成功', 200) : new Response('删除失败', 501)
    } catch (err) {
      throw new Response('插入异常', 500, err)
    }
  }

  // 信息录入 ---- 编辑
  async editStuInfo(params) {
    const { ctx } = this
    const { mysql } = this.app
    const { name, gender, age, tel, parentTel, stu_code } = params
    const sql = `UPDATE students SET stu_name=?, stu_gender=?, stu_age=?, tel=?, parentTel=? WHERE stu_code=?`
    const result = await mysql.query(sql, [name, gender, age, tel, parentTel, stu_code])
    ctx.body = result.affectedRows === 1 ? new Response('编辑成功', 200) : new Response('编辑失败', 500)
  }

  /**
   * 报名管理 ---- 新增订单
   * @param {*} order 学生code、课程（code、name、price）、老师（code、name）、order(status_code, status_name,order_time)
   */
  async addOrder(order) {
    const { ctx } = this
    const { mysql } = this.app
    const { stu_code, stu_name, cur_code, cur_name, cur_price, tea_code, tea_name } = order
    const status = {
      code: 1,
      name: '已付款',
    }
    const pay_time = new Date().toISOString().substring(0, 19)
    try {
      /**
       * 先检查有没有重复
       */
      const orderNum = await mysql.query(`SELECT COUNT(*) AS count FROM orders WHERE stu_code = ? AND cur_code = ?`, [
        stu_code,
        cur_code,
      ])
      if (orderNum[0].count) {
        ctx.body = new Response('已报名相同课程', 200, { repeat: true })
        return
      }
      const sql = `INSERT INTO orders(stu_code, stu_name, cur_code, cur_name, cur_price, order_statusCode, order_statusName, tea_code, tea_name, order_time, record)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
      await mysql.query(`UPDATE students SET curNameList = JSON_ARRAY_APPEND(curNameList, '$', ?) WHERE stu_code = ?`, [
        cur_name,
        stu_code,
      ])
      await mysql.query(`UPDATE students SET curCodeList = JSON_ARRAY_APPEND(curCodeList, '$', ?) WHERE stu_code = ?`, [
        cur_code,
        stu_code,
      ])
      const result = await mysql.query(sql, [
        stu_code,
        stu_name,
        cur_code,
        cur_name,
        cur_price,
        status.code,
        status.name,
        tea_code,
        tea_name,
        pay_time,
        0,
      ])
      ctx.body =
        result.affectedRows === 1 ? new Response('保存成功', 200, { repeat: false }) : new Response('保存失败', 501)
    } catch (err) {
      throw new Response('err', 500, err)
    }
  }

  // 报名管理 ---- 分页
  async queryOrderList(params) {
    const { ctx } = this
    const { mysql } = this.app
    const offSet = params.page === '1' ? 0 : (Number(params.page) - 1) * Number(params.pageSize)
    const pageSize = Number(params.pageSize)
    const cur_name = params?.cur_name ? params.cur_name : null
    const stu_name = params?.stu_name ? params.stu_name : null
    const sqlTotal = `SELECT COUNT(*) as cnt FROM orders 
      WHERE (cur_name LIKE CONCAT('%', ?, '%') OR ? IS NULL) 
        AND (stu_name LIKE CONCAT('%', ?, '%') OR ? IS NULL)`
    const sql = `SELECT * , DATE_FORMAT(order_time, '%Y-%m-%d %H:%i:%s') as orderTime FROM orders 
      WHERE (cur_name LIKE CONCAT('%', ?, '%') OR ? IS NULL) 
        AND (stu_name LIKE CONCAT('%', ?, '%') OR ? IS NULL) 
      LIMIT ? OFFSET ?`
    try {
      const total = await mysql.query(sqlTotal, [cur_name, cur_name, stu_name, stu_name])
      const orderList = await mysql.query(sql, [cur_name, cur_name, stu_name, stu_name, pageSize, offSet])
      ctx.body = new Response('查询成功', 200, { total: total[0].cnt, orderList })
    } catch (err) {
      throw new Response('err', 500, err)
    }
  }

  // 报名管理 ---- 申请删除
  async applyDelete(params) {
    const { ctx } = this
    const { mysql } = this.app
    const { order_id, remarks } = params
    const sql = `UPDATE orders SET order_statusCode=?, order_statusName=?, delete_reason=?, record=? WHERE order_id=?`
    try {
      const result = await mysql.query(sql, ['7', '待删除', remarks, 1, order_id])
      ctx.body = result.affectedRows === 1 ? new Response('操作成功', 200) : new Response('操作失败', 501)
    } catch (err) {
      throw new Response('error', 500, err)
    }
  }

  // 报名管理 ---- 申请退款
  async applyRefund(params) {
    const { ctx } = this
    const { mysql } = this.app
    const { order_id, remarks, refound_anount } = params
    const sql = `UPDATE orders SET order_statusCode=?, order_statusName=?, refound_reason=?, refund_amount=?, record=? WHERE order_id=?`
    try {
      const result = await mysql.query(sql, ['2', '待审批', remarks, refound_anount, 1, order_id])
      ctx.body = result.affectedRows === 1 ? new Response('操作成功', 200) : new Response('操作失败', 501)
    } catch (err) {
      throw new Response('error', 500, err)
    }
  }

  // 报名管理 ---- 已经退款
  async refund(params) {
    const { ctx } = this
    const { mysql } = this.app
    const sql = `UPDATE orders SET order_statusCode=?, order_statusName=? WHERE order_id=?`
    const result = await mysql.query(sql, [4, '已退款', params.order_id])
    ctx.body = result.affectedRows === 1 ? new Response('退款成功', 200) : new Response('退款失败', 200)
  }

  // 审批管理---- 分页数据
  async approveData(params) {
    const { ctx } = this
    const { mysql } = this.app
    const offSet = params.page === '1' ? 0 : (Number(params.page) - 1) * Number(params.pageSize)
    const pageSize = Number(params.pageSize)
    const cur_name = params?.cur_name ? params.cur_name : null
    const stu_name = params?.stu_name ? params.stu_name : null
    const sqlTotal = `SELECT COUNT(*) as cnt FROM orders 
    WHERE (cur_name LIKE CONCAT('%', ?, '%') OR ? IS NULL) 
      AND (stu_name LIKE CONCAT('%', ?, '%') OR ? IS NULL)
      AND order_statusCode=?
      AND record=1`
    const sql = `SELECT * FROM orders
    WHERE (cur_name LIKE CONCAT('%', ?, '%') OR ? IS NULL) 
      AND (stu_name LIKE CONCAT('%', ?, '%') OR ? IS NULL)
      AND order_statusCode=?
      AND record=1
    LIMIT ? OFFSET ?`
    try {
      const total = await mysql.query(sqlTotal, [cur_name, cur_name, stu_name, stu_name, Number(params.statusCode)])
      const orderList = await mysql.query(sql, [
        cur_name,
        cur_name,
        stu_name,
        stu_name,
        Number(params.statusCode),
        pageSize,
        offSet,
      ])
      ctx.body = new Response('查询成功', 200, { total: total[0].cnt, orderList })
    } catch (err) {
      throw new Response('err', 500, err)
    }
  }

  // 审批管理 ---- 审批通过
  async approveOrder(params) {
    const { ctx } = this
    const { mysql } = this.app
    const deleteSql = `delete from orders where order_id=?`
    const approveSql = `update orders set order_statusCode=?, order_statusName=? where order_id=?`
    try {
      if (params.order_statusCode === 2) {
        const result = await mysql.query(approveSql, [3, '待退款', params.order_id])
        ctx.body = result.affectedRows > 0 ? new Response('退款审批成功', 200) : new Response('退款审批失败', 500)
      } else {
        const result = await mysql.query(deleteSql, [params.order_id])
        ctx.body = result.affectedRows > 0 ? new Response('删除成功', 200) : new Response('删除失败', 500)
      }
    } catch (err) {
      throw new Response('异常错误', 500)
    }
  }

  // 审批管理 ---- 审批驳回
  async rejectOrder(params) {
    const { ctx } = this
    const { mysql } = this.app
    const sql = `update orders set order_statusCode=?, order_statusName=? where order_id=?`
    try {
      if (params.order_statusCode === 2) {
        const result = await mysql.query(sql, [6, '退款驳回', params.order_id])
        if (result.affectedRows > 0) {
          ctx.body = new Response('退款驳回成功', 200)
        } else {
          ctx.body = new Response('操作失败', 500)
        }
      } else {
        const result = await mysql.query(sql, [5, '删除驳回', params.order_id])
        console.log(result)
        if (result.affectedRows > 0) {
          ctx.body = new Response('删除驳回成功', 200)
        } else {
          ctx.body = new Response('操作失败', 500)
        }
      }
    } catch (err) {
      throw new Response('异常错误', 500)
    }
  }
}
module.exports = orderService
